package com.fingard.dsp.bank.directbank.cbs01;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import com.fingard.dsp.bank.directbank.bankInterface.IBatPayRec;
import com.fingard.dsp.bank.reqrespobj.batpayrec.ReqBatPayRec;
import com.fingard.dsp.bank.reqrespobj.batpayrec.ReqQryBatPayRec;
import com.fingard.dsp.bank.reqrespobj.batpayrec.RetAtsBatPayRec;
import com.fingard.dsp.bank.reqrespobj.batpayrec.RetAtsQryBatPayRec;
import com.fingard.dsp.bank.reqrespobj.batpayrec.detail.RetAtsBatPayRecDetail;
import com.fingard.text.SqlHelper;
import com.fingard.text.StringHelper;

public class CBS01BatPayRec extends CBS01Base implements IBatPayRec {
	// 1988
	public RetAtsBatPayRec d_1988_BatPay(ReqBatPayRec p_req1988) throws Exception {
		return d_BatPayRec(p_req1988);
	}

	// 9188
	public RetAtsBatPayRec d_9188_BatPay(ReqBatPayRec p_req9188) throws Exception {
		return d_BatPayRec(p_req9188);
	}

	// 批量代收和批量代付提交
	public RetAtsBatPayRec d_BatPayRec(ReqBatPayRec p_reqBat) throws Exception {
		// 返回ATS的对象
		RetAtsBatPayRec retAts = new RetAtsBatPayRec(p_reqBat.reqHeader);

		Statement st = null;
		Connection conn = getConnection();

		try {
			conn.setAutoCommit(false);
			st = conn.createStatement();
			int rowsCount = 0;

			String tableName = getBankConfig().getValueAsString("tableNameBatPayTOTAL");
			String insertSql = buildTOTALSql(tableName, p_reqBat);
			this.WriteBankLogLn2("执行insert sql语句：" + insertSql.toString());
			System.out.println(insertSql.toString());
			int affRows = st.executeUpdate(insertSql.toString());
			if (affRows != 1) {
				throw new Exception("写入影响行数与预期不符");
			}
			tableName = getBankConfig().getValueAsString("tableNameBatPayDETAIL");
			while (p_reqBat.readRecord()) {
				String recordSql = buildRecordSql(tableName, p_reqBat);
				System.out.println(recordSql);
				rowsCount += st.executeUpdate(recordSql.toString());
			}
			if (rowsCount == Integer.parseInt(p_reqBat.totalNum)) {
				conn.commit();
				retAts.respCode = "0";
				retAts.reqSeqID = p_reqBat.reqBatchNO;
				retAts.transState = "submitted";
				retAts.payInfoCode = "submitted";
				retAts.respInfo = "提交成功";
			} else {
				conn.rollback();
				retAts.respCode = "-1";
				retAts.respInfo = "总笔数不一致";
			}

		} catch (Exception e) {
			conn.rollback();
			retAts.respCode = "-1";
			retAts.respInfo = e.toString();
			e.printStackTrace();
		} finally {
			if (st != null) {
				st.close();
			}
			if (conn != null) {
				conn.close();
			}
		}
		if (retAts.respCode.equals("-1") && StringHelper.isNullOrEmpty(retAts.reqSeqID)) {
			retAts.reqSeqID = p_reqBat.reqBatchNO;
		}
		retAts.writeRetToATS();
		return retAts;
	}

	private String buildTOTALSql(String tableName, ReqBatPayRec p_reqBat) throws Exception {
		StringBuilder tmpSbSql = new StringBuilder();
		StringBuilder tmpSbValues = new StringBuilder();
		tmpSbSql.append("insert into " + tableName + " (PAY_ID, ");
		tmpSbValues.append(" values (" + SqlHelper.varToSql(p_reqBat.reqBatchNO) + ", ");
		tmpSbSql.append("OPR_TYPE, ");
		if (p_reqBat.reqHeader.transType.equals("1988")) {
			if(p_reqBat.reqHeader.commandCode.equals("198802")){
				tmpSbValues.append("'0'" + ", ");//0，代发工资
			}
			else{
				tmpSbValues.append("'1'" + ", ");//1，代发
			}
			tmpSbSql.append("AGENCY_ACCOUNT, ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.ownAct) + ", ");
		} else if (p_reqBat.reqHeader.transType.equals("9188")) {
			tmpSbValues.append("'2'" + ", ");//2，代扣
			tmpSbSql.append("AGENCY_ACCOUNT, ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.ownAct) + ", ");
		}
		tmpSbSql.append("TOTAL_AMOUNT, ");
		tmpSbValues.append(p_reqBat.totalAmt + ", ");
		tmpSbSql.append("PURPOSE, ");
		tmpSbValues.append(SqlHelper.varToSql(p_reqBat.useType) + ", ");
		// tmpSbSql.append("PRIORITY_FLAG"+", ");
		// tmpSbValues.append(SqlHelper.varToSql(p_reqBat.paySpd=="1"?"Y":"N")+",
		// ");
		String RECORD_STATUS = "Available";
		tmpSbSql.append("RECORD_STATUS" + ", ");
		tmpSbValues.append(SqlHelper.varToSql(RECORD_STATUS) + ", ");

		String NETBANK_TRANS_ID = "";
		String CHECK_CODE = getCHECK_CODE(p_reqBat.reqBatchNO, p_reqBat.ownAct, p_reqBat.totalAmt, NETBANK_TRANS_ID);
		tmpSbSql.append("CHECK_CODE" + ", ");
		tmpSbValues.append(SqlHelper.varToSql(CHECK_CODE) + ", ");
		tmpSbSql.append("VERSION" + ")");
		tmpSbValues.append("0" + ")");
		tmpSbSql.append(tmpSbValues);
		return tmpSbSql.toString();
	}

	private String buildRecordSql(String tableName, ReqBatPayRec p_reqBat) throws Exception {
		StringBuilder tmpSbSql = new StringBuilder();
		StringBuilder tmpSbValues = new StringBuilder();
		tmpSbSql.append("insert into " + tableName + " (PAYMENT_ID, ");
		tmpSbValues.append(" values (" + SqlHelper.varToSql(p_reqBat.reqBatchNO) + ", ");
		tmpSbSql.append("DETAIL_ID" + ", ");
		tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.askNumber) + ", ");
		tmpSbSql.append("NAME" + ", ");
		if (p_reqBat.reqHeader.transType.equals("1988")) {
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.recName) + ", ");
			tmpSbSql.append("ACCOUNT" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.recAct) + ", ");
			tmpSbSql.append("BANK" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.recBankName) + ", ");
			tmpSbSql.append("CITY_NAME" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.recAreaName) + ", ");
			tmpSbSql.append("BANK_CODE" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.recArea) + ", ");
		} else if (p_reqBat.reqHeader.transType.equals("9188")) {
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.payName) + ", ");
			tmpSbSql.append("ACCOUNT" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.payAct) + ", ");
			tmpSbSql.append("BANK" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.payBankName) + ", ");
			tmpSbSql.append("CITY_NAME" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.payAreaName) + ", ");
			tmpSbSql.append("BANK_CODE" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.payArea) + ", ");
		}
		tmpSbSql.append("AMOUNT" + ", ");
		tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.payAmount) + ", ");
		if (p_reqBat.readedDetail.certType.equals("0")) {
			tmpSbSql.append("CARD_ID" + ", ");
			tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.certNum) + ", ");
		}
		String card_flag = "";
		if(p_reqBat.readedDetail.certType.equals("1")){
			card_flag="10";
		}else if(p_reqBat.readedDetail.certType.equals("2")){
			if(p_reqBat.readedDetail.sameCity.equals("1")){
				card_flag="00";	//同城借记卡
			}else if(p_reqBat.readedDetail.sameCity.equals("0")){
				card_flag="01";	//异地借记卡
			}
		}else if(p_reqBat.readedDetail.certType.equals("3")){
			if(p_reqBat.readedDetail.sameCity.equals("1")){
				card_flag="02";	//同城信用卡
			}else if(p_reqBat.readedDetail.sameCity.equals("0")){
				card_flag="03";	//异地信用卡
			}
		}
		tmpSbSql.append("CARD_FLAG"+", ");
		tmpSbValues.append(SqlHelper.varToSql(card_flag)+", ");
		tmpSbSql.append("PAY_CODE"+", ");
		tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.authCode)+", ");//缴费编号，代扣时要用到，工行必输
		tmpSbSql.append("REMARK" + ", ");
		tmpSbValues.append(SqlHelper.varToSql(p_reqBat.readedDetail.memo) + ", ");
		String NETBANK_TRANS_ID = "";
		String CHECK_CODE = getCHECK_CODE(p_reqBat.reqBatchNO, p_reqBat.ownAct, p_reqBat.totalAmt, NETBANK_TRANS_ID);
		tmpSbSql.append("CHECK_CODE" + ", ");
		tmpSbValues.append(SqlHelper.varToSql(CHECK_CODE) + ", ");
		tmpSbSql.append("VERSION" + ")");
		tmpSbValues.append("0" + ")");
		tmpSbSql.append(tmpSbValues);
		return tmpSbSql.toString();
	}

	// 1998 状态查询
	public RetAtsQryBatPayRec d_1998_QryBatPay(ReqQryBatPayRec p_req1998) throws Exception {
		return d_QryBatPayRec(p_req1998);
	}

	// 9198
	public RetAtsQryBatPayRec d_9198_QryBatPay(ReqQryBatPayRec p_req9198) throws Exception {
		return d_QryBatPayRec(p_req9198);
	}

	// 批量代收和批量代付状态查询
	public RetAtsQryBatPayRec d_QryBatPayRec(ReqQryBatPayRec p_reqQryBat) throws Exception {
		// 返回ATS的对象
		RetAtsQryBatPayRec retAts = new RetAtsQryBatPayRec(p_reqQryBat.reqHeader);
		retAts.respCode = "";

		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet result = null;

		try {
			conn = getConnection();
			String tableName = getBankConfig().getValueAsString("tableNameBatPayTOTAL");
			String sql = "select * from "+tableName+" where PAY_ID=?";
			this.WriteBankLogLn2("执行select sql语句：select * from "+tableName+" where PAY_ID='"+p_reqQryBat.reqBatchNO+"'");
			System.out.println("执行select sql语句：select * from "+tableName+" where PAY_ID='"+p_reqQryBat.reqBatchNO+"'");
	        pre = conn.prepareStatement(sql);
	        pre.setString(1, p_reqQryBat.reqBatchNO);
	        result = pre.executeQuery();
	        if(result.next()){
	        	retAts.respCode = "0";
				retAts.reqSeqID = p_reqQryBat.reqBatchNO;
				retAts.transState = result.getString("RECORD_STATUS");
				retAts.respInfo = "";
				retAts.payInfoCode = retAts.transState;
				retAts.writeBeforeDetail();
				tableName = getBankConfig().getValueAsString("tableNameBatPayDETAIL");
				sql = "select * from " + tableName + " where PAYMENT_ID=?";

				result.close();
				result = null;
				pre.close();
				pre = null;
				pre = conn.prepareStatement(sql);
				pre.setString(1, p_reqQryBat.reqBatchNO);
				result = pre.executeQuery();
				while (result.next()) {
					RetAtsBatPayRecDetail tmpRetDetail = new RetAtsBatPayRecDetail();
					if (p_reqQryBat.reqHeader.transType.equals("1998")) {
						tmpRetDetail.recAct = result.getString("ACCOUNT");
						tmpRetDetail.recName = result.getString("NAME");
					} else if (p_reqQryBat.reqHeader.transType.equals("9198")) {
						tmpRetDetail.payAct = result.getString("ACCOUNT");
						tmpRetDetail.payName = result.getString("NAME");
					}
					tmpRetDetail.payAmount = result.getString("AMOUNT");
					tmpRetDetail.result = result.getString("TRANS_STATUS");
					tmpRetDetail.memo = result.getString("REMARK");
					tmpRetDetail.result = tmpRetDetail.result;
					retAts.writeRetDetail(tmpRetDetail);
				}
			} else {
				result.close();
				result = null;
				pre.close();
				pre = null;
				sql = "select count(*) from " + tableName + " where PAY_ID=?";
				pre = conn.prepareStatement(sql);
				pre.setString(1, p_reqQryBat.reqBatchNO);
				result = pre.executeQuery();
				if (result.next()) {
					if (result.getInt(1) == 0) {
						retAts.respCode = "0";
						retAts.reqSeqID = p_reqQryBat.reqBatchNO;
						retAts.respInfo = "记录不存在";
						retAts.payInfoCode = "notExist";
						retAts.transState = "notExist";
					} else {
						retAts.respCode = "-1";
						retAts.respInfo = "数据库记录与预期不符";
					}
				} else {
					retAts.respCode = "-1";
					retAts.respInfo = "查询数据库记录出现意外情况";
				}
			}
		} catch (Exception e) {
			retAts.respCode = "-1";
			retAts.respInfo = e.getMessage();
			e.printStackTrace();
		} finally {
			try {
				if (result != null)
					result.close();
				if (pre != null)
					pre.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (retAts.respCode.equals("-1") && StringHelper.isNullOrEmpty(retAts.reqSeqID)) {
			retAts.reqSeqID = p_reqQryBat.reqBatchNO;
		}
		if (retAts.respCode.equals("-1")) {
			retAts.writeBeforeDetail();
		}
		retAts.writeAfterDetail();
		return retAts;
	}
}
